package com.redtree.air.db
{
	import com.redtree.air.db.Constant;
	
	import mx.collections.ArrayCollection;

	public class Notes extends Model
	{

		public function Notes()
		{
			super();
			dataGridColumnsAry = [['title','标题'],['content','内容'],['tipName','提示方式'],['tipedString','是否提示'],['dateTipString','提示时间']];
			tableName = getTableName('notes');
		}
		public function getNotes(offset:int=0,limit:int=DEFAULT_PAGE_SIZE,underSecurity:Boolean=true,justCurrentLevel:Boolean=false):Array
		{
			var sql:String = "select n.*,s.name as security_name from notes as n " +
				"left join security as s on s.level_id=n.security_level";
			if( underSecurity )
				sql += " where n.security_level "+(justCurrentLevel?"=":"<=")+" "+currentSecurityLevel;
			sql += " ORDER BY n.id DESC";
			if( limit )
				sql += " limit "+limit+" offset "+offset;			
			return _getNotesBySql(sql);
		}
		public function _getNotesBySql(sql:String):Array
		{
			if(Model.instance.query(sql))var result:Array = Model.instance.data;
			var resultAry:Array = new Array();
			for( var i:int=0;i<result.length; i++ )
				if(result[i]['rows']==null)resultAry.push(new Note(result[i]));
			return resultAry;
		}
		public function getLevelReferData(offset:int=0,limit:int=DEFAULT_PAGE_SIZE):Array
		{
			return getNotes(offset,limit,true,true);
		}
		public function getReferTotalCount():int
		{
			return getTotalCount(true,true);
		}
		public function getTotalCount(underSecurity:Boolean=true,justCurrentLevel:Boolean=false):int{
			var sql:String = "select count(*) as num from notes as n";
			if( underSecurity )
				sql += " where n.security_level "+(justCurrentLevel?"=":"<=")+" "+currentSecurityLevel;
			Model.instance.query(sql);
			
			return Model.instance.data[0]['num'];
			
		}
		/**
		 * 添加记事本
		 */
		public function addNote( title:String, content:String, securityLevel:int, tipType:int, timeTip:String ):Boolean
		{
			var sql:String = "INSERT INTO notes (title,content,security_level,tip,time_tip) values "
				+'("'+title+'","'+content+'",'+securityLevel+','+tipType+',"'+timeTip+'")';
			return Model.instance.query(sql);
		}
		/**
		 * 获取提示方式
		 *
		 */
		public function getTipTypes():Array
		{
			var ary:Array = [
				{label:'无提示',data:Constant.TIP_TYPE_NULL},
				{label:'弹出窗提示',data:Constant.TIP_TYPE_POPUP},
				{label:'短信提示',data:Constant.TIP_TYPE_SMS},
				{label:'弹窗和短信提示',data:Constant.TIP_TYPE_POPUP_SMS}
			];			
			return ary;
		}
		/**
		 * 获得待提示的数据
		 * @param 提示类型
		 */
		public function getTipRecords(minitesUpto:int=Constant.TIP_MINUTES_NO,tipType:int=Constant.TIP_TYPE_NULL):Array
		{
			var condition:String =
				(tipType!=Constant.TIP_TYPE_NULL?"tip & "+tipType+" ="+tipType+" and":"") +
				(minitesUpto==Constant.TIP_MINUTES_NO?
					" datetime(time_tip) > datetime(datetime(\"now\",\"+8 hours\"),\"-5 seconds\") " +
					" and datetime(time_tip)<=datetime(datetime(\"now\",\"+8 hours\"),\"+10 seconds\") "					
					:" datetime(time_tip) > datetime(\"now\",\"+8 hours\")" +
					" and datetime(time_tip)<=datetime(datetime(\"now\",\"+8 hours\"),\"+"+minitesUpto+" minutes\") group by time_tip")
			var sql:String = "select * from notes where tiped="+Constant.TIP_UNTIPED+" and " + condition;
			
			var result:Array = this._getNotesBySql(sql);
			if( minitesUpto==Constant.TIP_MINUTES_NO )
				this.tipedRecords(condition);
			return result;
		}
		/**
		 * 标记信息已经提示过了
		 */
		public function tipedRecords(condition:String):Boolean
		{
			var sql:String = "update notes set tiped="+Constant.TIP_TIPED + " where " + condition;
			return Model.instance.query(sql);
		}
		/**
		 * 得到最新的提示，供欢迎界面
		 */
		public function getLatestTip():Array
		{
			var sql:String = "select * from notes where tiped="+Constant.TIP_UNTIPED+" and datetime(time_tip) > datetime(\"now\",\"+8 hours\") order by id ASC limit 1";
			return this._getNotesBySql(sql);
		}
	}
}